package db;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;

import model.City;
import model.Country;
import model.ItemSupplier;
import model.Outsider;

public class DBItemSupplier implements IFItemSupplier
{
	private Connection con;

	public DBItemSupplier()
	{
		con = DBConnection.getInstance().getDBcon();
	}

	@Override
	public int updateItemSupplierStatus(int itemID, int supplierID) throws SQLException
	{
		int result = -1;

		String query = "UPDATE item_supplier SET is_outsider_active = CASE is_outsider_active WHEN 0 THEN 1 ELSE 0 END WHERE is_item_id = ? AND is_outsider_id = ?";

		if (DBConnection.doDebug())
			DBConnection.printDebug("updateItemSupplierStatus", query);

		try
		{
			PreparedStatement ps = con.prepareStatement(query);

			ps.setInt(1, itemID);
			ps.setInt(2, supplierID);

			result = ps.executeUpdate();

			ps.close();
		}
		catch (SQLException e)
		{
			if (DBConnection.doDebug())
			{
				System.out.println("[ERROR] Query exception - updateItemSupplierStatus - UPDATE: " + e);
				e.printStackTrace();
			}
		}

		return result;
	}

	@Override
	public ArrayList<ItemSupplier> getItemSuppliers(int itemID, boolean active) throws SQLException
	{
		ArrayList<ItemSupplier> supplierList = new ArrayList<ItemSupplier>();

		String query = "SELECT o.outsider_id, o.outsider_name, cn.country_name, cn.country_tld, isp.is_item_quantity, isp.is_outsider_active FROM item_supplier AS isp LEFT JOIN outsider AS o ON isp.is_outsider_id = o.outsider_id LEFT JOIN city ON o.outsider_city_id = city.city_id LEFT JOIN country AS cn ON city.city_country_id = cn.country_id WHERE isp.is_item_id = ?" + (active ? " AND isp.is_outsider_active = 1" : "") + " ORDER BY isp.is_outsider_active DESC";		

		if (DBConnection.doDebug())
			DBConnection.printDebug("getItemSuppliers", query);

		try
		{
			PreparedStatement ps = con.prepareStatement(query);

			ps.setInt(1, itemID);

			ResultSet results = ps.executeQuery();

			while(results.next())
			{
				Outsider newSupplier = new Outsider(results.getInt("outsider_id"), results.getString("outsider_name"), new City(new Country(results.getString("country_name"), results.getString("country_tld"))));
				int quantity = results.getInt("is_item_quantity");
				boolean isActive = results.getBoolean("is_outsider_active");

				supplierList.add(new ItemSupplier(newSupplier, quantity, isActive));
			}

			ps.close();
		}
		catch (SQLException e)
		{
			if (DBConnection.doDebug())
			{
				System.out.println("[ERROR] Query exception - getItemSuppliers - SELECT: " + e);
				e.printStackTrace();
			}
		}

		return supplierList;
	}
}